﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;
using YouHP.Utility;
using System.Data.SqlClient;

namespace YouHP.SqlServerDAL
{
    public class PatientWeightDAL : IPatientWeight
    {
        #region private functions
        // 添加单条的体重历史记录,采用事务方式
        private int AddWeightHistory(SqlTransaction ts, WeightHistoryInfo info)
        {
            //判断当前对应的记录是否已经存在，如存在，则执行更新操作
            int historyId = GetWeightHistory(ts, info.PRId, info.RecordDate);
            if (historyId > 0)
            {
                //执行update操作，覆盖原来的数据
                info.Id = historyId;
                UpdateWeightHistory(ts, info);
                return historyId;
            }

            SqlParameter[] outParms = new SqlParameter[]  {
                new SqlParameter("@PRId", SqlDbType.Int),
                new SqlParameter("@DataValue", SqlDbType.Float),
                new SqlParameter("@RecordDate", SqlDbType.DateTime),
                new SqlParameter("@UpdateDate", SqlDbType.DateTime),
                new SqlParameter("@Id", SqlDbType.Int)
            };
            outParms[0].Value = info.PRId;
            outParms[1].Value = info.DataValue;
            outParms[2].Value = info.RecordDate;
            outParms[3].Value = info.UpdateDate;
            outParms[4].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure, Common.PatientWeightSP_AddWeightHistory, outParms);

            return Convert.ToInt32(outParms[4].Value.ToString());
        }

        // 修改单条的症状历史记录,采用事务方式
        private void UpdateWeightHistory(SqlTransaction ts, WeightHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientWeightSP_UpdateWeightHistory,
                info.Id,
                info.PRId,
                info.DataValue,
                info.RecordDate,
                info.UpdateDate
                );

            return;
        }

        // 删除当前记录表中的纪录,采用事务方式
        private bool DeleteCurrentWeight(SqlTransaction ts, int prId)
        {
            string condStr = string.Format(@" [PatientCurrentWeight].[PRId]={0} ",
                prId);

            return SqlHelper.ExecuteNonQuery(ts,
                Common.PatientWeightSP_DelCurrentWeight,
                condStr.ToString()) > 0;
        }

        // 添加当前的症状记录, 采用事务方式
        private void AddCurrentWeight(SqlTransaction ts, WeightHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientWeightSP_AddCurrentWeight,
                info.PRId,
                info.DataValue,
                info.WeightHistoryId
                );

            return;
        }

        // 修改当前的症状记录, 采用事务方式
        private void UpdateCurrentWeight(SqlTransaction ts, WeightHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientWeightSP_UpdateCurrentWeight,
                info.PRId,
                info.DataValue,
                info.WeightHistoryId
                );

            return;
        }

        // 根据历史Id删除症状历史记录信息,采用事务方式
        private bool DeleteWeightHistory(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientWeightHistory].[PRId]={0} AND [PatientWeightHistory].[Id]={1}",
                prId, historyId);

            return SqlHelper.ExecuteNonQuery(ts,
                Common.PatientWeightSP_DelWeightHistory,
                condStr.ToString()) > 0;
        }

        // 检查当前对应的记录是否已经存在
        private int GetWeightHistory(SqlTransaction ts, int prId, DateTime recordDate)
        {
            string condStr = string.Format(@" [PatientWeightHistory].[PRId]={0} AND "
                + " [PatientWeightHistory].[RecordDate]='{1}' ", prId, recordDate);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientWeightSP_GetWeightHistory,
                condStr) as IDataReader;
            WeightHistoryInfo shInfo = CBO.FillObject<WeightHistoryInfo>(reader);

            return shInfo.Id;
        }

        // 取得记录表中最早的一条
        private WeightHistoryInfo GetLasttestWeightHistory(SqlTransaction ts, int prId)
        {
            string condStr = string.Format(@" [PatientWeightHistory].[PRId]={0} AND "
                + " [PatientWeightHistory].[RecordDate]=( SELECT MAX(RecordDate) from PatientWeightHistory "
                + " WHERE [PatientWeightHistory].[PRId] = {0} ) ", prId);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientWeightSP_GetWeightHistory,
                condStr) as IDataReader;
            return CBO.FillObject<WeightHistoryInfo>(reader);
        }

        // 根据纪录时间来判断当前记录是否为历史纪录中最早的纪录
        private bool CheckLasttestWeightHistory(SqlTransaction ts, int prId, DateTime recordDate)
        {
            bool retVal = true;

            string condStr = string.Format(@" [PatientWeightHistory].[PRId]={0} AND [PatientWeightHistory].[RecordDate] > '{1}'",
                    prId, recordDate);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientWeightSP_GetWeightHistoryCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = false; //存在则说明,该记录不是最新的记录
                }
            }
            dr.Close();

            return retVal;
        }

        // 判断当前纪录表中是否已存在纪录
        private bool CheckExsitCurrentWeightHistory(SqlTransaction ts, int prId)
        {
            bool retVal = false;

            string condStr = string.Format(@" [PatientCurrentWeight].[PRId]={0} ",  prId);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientWeightSP_GetCurrentWeightCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = true;
                }
            }
            dr.Close();

            return retVal;
        }

        // 在当前记录表查找某条历史纪录
        private WeightHistoryInfo GetCurrentWeight(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientCurrentWeight].[PRId]={0} AND [PatientCurrentWeight].[WeightHistoryId]={1}",
                prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientWeightSP_GetCurrentWeight,
                condStr) as IDataReader;
            return CBO.FillObject<WeightHistoryInfo>(reader);
        }

        // 判断当前纪录表中是否已存在纪录
        private bool CheckExsitCurrentWeightHistoryByHistoryId(SqlTransaction ts, int prId, int historyId)
        {
            bool retVal = false;

            string condStr = string.Format(@" [PatientCurrentWeight].[PRId]={0} AND [PatientCurrentWeight].[WeightHistoryId]={1} ",
                prId, historyId);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientWeightSP_GetCurrentWeightCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = true;
                }
            }
            dr.Close();

            return retVal;
        }
        #endregion

        //判断历史记录表中是否已存在病人指定时间对应的记录
        public bool CheckWeightHistory(int prId, DateTime recordDate)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                int whId = GetWeightHistory(ts, prId, recordDate);
                if (whId > 0)
                {
                    retVal = true;
                }

                ts.Commit();
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 获得病人当前体重
        public WeightHistoryInfo GetCurrentWeight(int prId)
        {
            string condStr = string.Format(@" [PatientCurrentWeight].[PRId]={0} ", prId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientWeightSP_GetCurrentWeight,
                condStr) as IDataReader;
            return CBO.FillObject<WeightHistoryInfo>(reader);
        }

        // 添加病人体重历史
        public bool AddWeightHistory(WeightHistoryInfo info)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                info.WeightHistoryId = AddWeightHistory(ts, info);

                //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                if (CheckLasttestWeightHistory(ts, info.PRId, info.RecordDate))
                {
                    //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                    if (CheckExsitCurrentWeightHistory(ts, info.PRId))
                    {
                        //执行修改
                        UpdateCurrentWeight(ts, info);
                    }
                    else
                    {
                        //执行添加
                        AddCurrentWeight(ts, info);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 获得病人的体重历史
        public WeightHistoryInfo GetWeightHistory(int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientWeightHistory].[PRId]={0} AND [PatientWeightHistory].[Id]={1}",
                prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientWeightSP_GetWeightHistory,
                condStr) as IDataReader;
            return CBO.FillObject<WeightHistoryInfo>(reader);
        }        
        
        // 修改病人体重历史
        public bool UpdateWeightHistory(WeightHistoryInfo info)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                UpdateWeightHistory(ts, info);
                info.WeightHistoryId = info.Id;    //为操作当前记录准备数据

                //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                if (CheckLasttestWeightHistory(ts, info.PRId, info.RecordDate))
                {
                    //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                    if (CheckExsitCurrentWeightHistory(ts, info.PRId))
                    {
                        //执行修改
                        UpdateCurrentWeight(ts, info);
                    }
                    else
                    {
                        //执行添加
                        AddCurrentWeight(ts, info);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 删除病人体重历史
        public bool DeleteWeightHistory(int prId, int historyId)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                //在当前记录表查找该历史纪录(最好是先取,再删除)
                WeightHistoryInfo whInfo = GetCurrentWeight(ts, prId, historyId);
                //从历史纪录表中删除该纪录
                DeleteWeightHistory(ts, prId, historyId);

                if (whInfo.Id > 0)   //需要对当前记录进行操作
                {
                    //从历史纪录中再选出一条最新的记录
                    WeightHistoryInfo lasttestInfo = GetLasttestWeightHistory(ts, prId);
                    if (lasttestInfo.Id <= 0)   //如果找不到,则将当前记录表中的内容删除
                    {
                        DeleteCurrentWeight(ts, prId);
                    }
                    else  //如果找到,则修改当前记录表中的数据
                    {
                        lasttestInfo.WeightHistoryId = lasttestInfo.Id;
                        UpdateCurrentWeight(ts, lasttestInfo);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 获得病人体重历史纪录列表
        public List<WeightHistoryInfo> GetWeightHistoryList(int prId, int pageSize, int pageIndex, out int count)
        {
            string condStr = string.Format(@" [PatientWeightHistory].[PRId]={0} ", prId);
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientWeightSP_GetWeightHistoryList,
                pageSize, pageIndex, condStr, " [PatientWeightHistory].[RecordDate] desc ");

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
            }
            else
            {
                count = 0;
            }
            dr.Close();
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<WeightHistoryInfo>(dr);
        }

        //取得某段时间内的所有症状记录历史
        public List<WeightHistoryInfo> GetWeightHistoryList(int prId, DateTime startDate, DateTime endDate)
        {
            string condStr = string.Format(@" [PatientWeightHistory].[PRId]={0} AND [PatientWeightHistory].[RecordDate] >= '{1}' " +
                " AND [PatientWeightHistory].[RecordDate] <= '{2}' ", prId, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
            string orderStr = " [PatientWeightHistory].[RecordDate] asc ";

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientWeightSP_GetAllWeightHistory,
                condStr, orderStr);

            return CBO.FillCollection<WeightHistoryInfo>(dr);
        }

        public List<WeightHistoryInfo> GetWeightHistoryList(int prId)
        {
            string condStr = string.Format(@" [PatientWeightHistory].[PRId]={0} ", prId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientWeightSP_GetAllWeightHistory,
                condStr, " [PatientWeightHistory].[RecordDate] asc ") as IDataReader;
            return CBO.FillCollection<WeightHistoryInfo>(reader);
        }

        #region 病人身高
        public bool UpdatePatientHeight(int patientId, double height)
        {
            return SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientWeightSP_UpdatePatientHeight,
                patientId,
                height
                ) > 0 ;
        }

        public double GetPatientHeight(int patientId)
        {
            double retVal = 0.0 ;
            try
            {
                IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                    Common.PatientWeightSP_GetPatientHeight,
                    patientId
                    );

                if (dr != null && dr.Read())
                {
                    retVal = dr.GetDouble(0);
                }
                dr.Close();

            }
            catch (Exception )
            { 
            }
            return retVal;
        }
        #endregion
    }
}
